package org.liaoxuefeng.study;

import lombok.val;
import org.testng.annotations.Test;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author daiyongfei
 * <p>
 * JDBC连接
 */
public class JDBCConnection {

    //5.7版本
    // final String JDBC_URL = "jdbc:mysql://123.57.163.123/jdbc?useSSL=false&serverTimezone=GMT%2B8";
    //8.0版本 本地数据库
    final String JDBC_URL = "jdbc:mysql://localhost/jdbc?useSSL=false&serverTimezone=GMT%2B8";
    final String JDBC_USER = "root";
    final String JDBC_PASSWORD = "root";


    /**
     * 创建连接
     */
    @Test
    public void test() throws SQLException {
        //创建连接
        val connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
        System.out.println("连接成功！！");
        connection.close();
    }


    /**
     * JDBC查询
     */
    @Test
    public void test1() throws SQLException {
        //获取连接
        val connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
        //创建声明
        Statement statement = connection.createStatement();
        //查询sql语句
        String sql = "SELECT id, grade, name, gender FROM students WHERE gender=1";
        //执行查询sql语句
        val result = statement.executeQuery(sql);
        //遍历查询返回的数据
        while (result.next()) {
            //根据索引获取每一条数据   注意：索引从1开始
            long id = result.getLong(1);
            System.out.print("id: " + id);
            long grade = result.getLong(2);
            System.out.print("  grade: " + grade);
            String name = result.getString(3);
            System.out.print("  name: " + name);
            int gender = result.getInt(4);
            System.out.println("  gender: " + gender);
        }

        statement.close();
        connection.close();
    }


    /**
     * JDBC查询
     * 防止sql注入
     */
    @Test
    public void test2() throws SQLException {
        //获取连接
        val connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
        //查询sql语句
        String sql = "SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?";
        //创建预编译声明
        val preparedStatement = connection.prepareStatement(sql);
        //给查询条件赋值   索引从1开始
        preparedStatement.setObject(1, (byte) 1);
        preparedStatement.setObject(2, 3);

        //执行查询sql语句(不用传参数)
        val result = preparedStatement.executeQuery();
        //遍历查询返回的数据
        while (result.next()) {
            //根据索引获取每一条数据   注意：索引从1开始
            long id = result.getLong(1);
            System.out.print("id: " + id);
            long grade = result.getLong(2);
            System.out.print("  grade: " + grade);
            String name = result.getString(3);
            System.out.print("  name: " + name);
            int gender = result.getInt(4);
            System.out.println("  gender: " + gender);
        }

        preparedStatement.close();
        connection.close();
    }

}
